package com.api.gbhg.xxd.api;

import com.alibaba.fastjson.JSON;
import com.api.gbhg.xxd.dao.GeneralDao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;


import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 预算执行通知单
 */
@Path("/BudGet")
public class BudgetExecutionNotice {


    //输出日志
    //private static final Logger logger = LoggerFactory.getLogger(BudgetExecutionNotice.class);
    private static final Logger logger = LoggerFactory.getLogger("gbhg");
    GeneralDao generalDao = new GeneralDao();


    /**
     * table1
     * @param mapParam
     * @return
     */
    public List capitalSource(Map<String,Object> mapParam){
        logger.info("===========预算执行通知单接口日志输出===========");
        String requestId = mapParam.get("requestId").toString();
        if(requestId.contains(",")){
            requestId = requestId.substring(0,requestId.indexOf(','));
        }
        String workflowId = generalDao.getRequestIdDao(requestId);
        logger.info("workflowId:"+workflowId);

        if(workflowId.isEmpty() || workflowId.equals("")){
            logger.info("该请求requestid没有关联的workflowId流程id  workflowId="+workflowId);
            return new ArrayList<>();
        }
        String tableName = generalDao.getTableNameByWorkFlowId(workflowId);
        BaseBean baseBean = new BaseBean();
        String YS27Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS27Workflowid");//费用信息和支付信息主表
        String YS10Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS10Workflowid");//费用信息和支付信息主表
        String YS06Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS06Workflowid");//费用信息主表
        String sql = "";
        RecordSet rs = new RecordSet();
//        String sqlId = "select id from "+tableName+" where requestid = " + mapParam.get("requestId")+"";
//        logger.info("根据requestid获取表单id  sqlId:"+sqlId);
//        rs.executeQuery(sqlId);
//        rs.next();
        if(YS27Workflowid.equals(workflowId) || YS10Workflowid.equals(workflowId)){
            sql = "select DISTINCT ub.fkyx,fkzh,xzzfl from  "+tableName+" fm \n" +
                    "LEFT JOIN uf_zyczbksr_xj uzx on fm.zcgnkm = uzx.id \n" +
                    "LEFT JOIN uf_bxzfyh ub on fm.fkyx = ub.id\n" +
                    "where fm.requestid = "+requestId;
        }else {
            sql = "select DISTINCT ub.fkyx,fkzh,xzzfl from "+tableName+"_dt2 fmd2\n" +
                    "LEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id\n" +
                    "LEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id \n" +
                    "LEFT JOIN uf_bxzfyh ub on fmd2.fkyx = ub.id\n" +
                    "where fm.requestid = "+requestId+"";
        }
        logger.info("table1 sql语句打印:"+sql);
        rs.executeQuery(sql);
        //存储返回的数据
        List list = new ArrayList();
        while (rs.next()){
            Map map = new HashMap();
//            map.put("xzzfl",rs.getString("xzzfl")); //支付令（无则空）	(表字段：选择支付令)
            map.put("fkyx",rs.getString("fkyx"));   //	付款银行
            map.put("fkzh",rs.getString("fkzh"));   //	付款账号

            if(!rs.getString("xzzfl").isEmpty() && !rs.getString("xzzfl").equals("")){
                map.put("zjly","财政拨款");    //资金来源
            }else{
                map.put("zjly","非财政拨款");    //资金来源
            }

            list.add(map);
        }
        List newList = new ArrayList();
        for(int i=0;i<list.size();i++){
            if(!newList.contains(list.get(i))){
                newList.add(list.get(i));
            }
        }
        logger.info("数据list:"+JSON.toJSON(list));
        logger.info("数据list new:"+JSON.toJSON(newList));
        return newList;
    }

    /**
     * table2：对公
     * @param mapParam
     * @return
     */
    public List publicTransfer(Map<String,Object> mapParam){
        logger.info("===========预算执行通知单接口日志输出===========");
        String requestId = mapParam.get("requestId").toString();
        if(requestId.contains(",")){
            requestId = requestId.substring(0,requestId.indexOf(','));
        }
        String workflowId = generalDao.getRequestIdDao(requestId);
        logger.info("workflowId:"+workflowId);

        if(workflowId.isEmpty() || workflowId.equals("")){
            logger.info("该请求requestid没有关联的workflowId流程id  workflowId="+workflowId);
            return new ArrayList<>();
        }
        String tableName = generalDao.getTableNameByWorkFlowId(workflowId);
        String billid = generalDao.getBillidByRequestid(tableName, requestId);

        RecordSet rs = new RecordSet();
        String sqlId = "select id from "+tableName+" where requestid = " + mapParam.get("requestId")+"";
        logger.info("根据requestid获取表单id  sqlId:"+sqlId);
        rs.executeQuery(sqlId);
        rs.next();
        String sql = "";
        BaseBean baseBean = new BaseBean();
        String YS27Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS27Workflowid");//费用信息和支付信息主表
        String YS10Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS10Workflowid");//费用信息和支付信息主表
        String YS06Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS06Workflowid");//费用信息主表

        if(YS27Workflowid.equals(workflowId) || YS10Workflowid.equals(workflowId)){
            //借款流程
            sql = "select 1 as 'bishu',sqbxje as 'zonghe',skr as skdwmc,skfzh,skfkhx,sqbxje,zflwb as xzzfl,sqbxje as tyzfje,\n" +
                    "(case zcqd when 0 then(select xmdm  from uf_zyczbksr_xj where id=(select REPLACE(hpssjid,'28_','') from uf_gxmdygnkm where id=xzkmdm )) \n" +
                    "else (select xmdm from uf_fzysrxm where id=zcdfczbkxm) end)as xmdm\n" +
                    "from "+tableName+" where zzlx=0 and requestid="+requestId;
        }else{
            sql = "select (select COUNT(*) jilu from "+tableName+"_dt2 fmd2  \n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id  \n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id \n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 0 and fm.requestid = "+requestId+") bishu, \n" +
                    "\t(select SUM(tyzfje) from "+tableName+"_dt2 fmd2 \n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id \n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id \n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 0 and fm.requestid = "+requestId+") zonghe, \n" +
                    "\tfmd2.skr as skdwmc,skfzh,skfkhx,tyzfje,zflwb as xzzfl,uzx.xmdm \n" +
                    "\tfrom "+tableName+"_dt2 fmd2 \n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id \n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id \n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 0 and fm.requestid = "+requestId+"";
        }

        logger.info("对公sql语句打印:"+sql);
        rs.executeQuery(sql);
        //存储返回的数据
        List list = new ArrayList();
        RecordSet getXMDM = new RecordSet();
        while (rs.next()){
            Map map = new HashMap();
            map.put("bishu",rs.getString("bishu")); //笔数
            map.put("zonghe",rs.getString("zonghe"));   //对公金额总和
            map.put("skdwmc",rs.getString("skdwmc")); //收款户名 （表字段：收款单位名称）
            map.put("skfzh",rs.getString("skfzh")); //收款方账号
            map.put("skfkhx",rs.getString("skfkhx"));//	收款方开户行
            map.put("tyzfje",rs.getString("tyzfje"));   //收款金额(表字段：同意支付金额)
            map.put("xzzfl",rs.getString("xzzfl")); //支付令（无则空）	(表字段：选择支付令)
            //map.put("xmdm",rs.getString("xmdm"));   //项目编码
            map.put("xmdm","");   //项目编码
            if(workflowId.equals(YS06Workflowid) || workflowId.equals(YS10Workflowid) || workflowId.equals(YS27Workflowid) ){
                String xmdmSQL = "select  uzx.xmdm  from "+tableName+" fm left join uf_gxmdygnkm ug on fm.xzkmdm=ug.id\n" +
                                "left join uf_zyczbksr_xj uzx on uzx.id=REPLACE(ug.hpssjid,'28_','')\n" +
                                "where fm.requestid="+requestId;
                getXMDM.execute(xmdmSQL);
                if (getXMDM.next()){
                    map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                }
            }else{
                String sql1 = "select top 1 xzkmdm from "+tableName+"_dt1 where mainid="+billid+" and zfl='"+rs.getString("xzzfl")+"'";
                getXMDM.execute(sql1);
                if(getXMDM.next()){
                    String xzkmdm = getXMDM.getString("xzkmdm");
                    String sql2 = "select a.xmdm from uf_zyczbksr_xj a, uf_gxmdygnkm b where b.id='"+xzkmdm+"' and a.id=REPLACE(b.hpssjid,'28_','')";
                    getXMDM.execute(sql2);
                    if(getXMDM.next()){
                        map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                    }
                }
            }
            list.add(map);
        }
        logger.info("数据list:"+JSON.toJSON(list));
        return list;
    }


    /**
     * table3：对私
     * @param mapParam
     * @return
     */
    public List forThePrivate(Map<String,Object> mapParam){
        logger.info("===========预算执行通知单接口日志输出 对私===========");
        String requestId = mapParam.get("requestId").toString();
        if(requestId.contains(",")){
            requestId = requestId.substring(0,requestId.indexOf(','));
        }
        String workflowId = generalDao.getRequestIdDao(requestId);
        logger.info("workflowId:"+workflowId);

        if(workflowId.isEmpty() || workflowId.equals("")){
            logger.info("该请求requestid没有关联的workflowId流程id  workflowId="+workflowId);
            return new ArrayList<>();
        }
        String tableName = generalDao.getTableNameByWorkFlowId(workflowId);
        String billid = generalDao.getBillidByRequestid(tableName,requestId);
        RecordSet rs = new RecordSet();
        String sqlId = "select id from "+tableName+" where requestid = " + mapParam.get("requestId")+"";

        rs.executeQuery(sqlId);
        rs.next();
        String sql = "";
        BaseBean baseBean = new BaseBean();
        String YS27Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS27Workflowid");//费用信息和支付信息主表
        String YS10Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS10Workflowid");//费用信息和支付信息主表
        String YS06Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS06Workflowid");//费用信息主表

        if(YS27Workflowid.equals(workflowId) || YS10Workflowid.equals(workflowId)){
            //借款流程
            sql = "select 1 as 'bishu',sqbxje as 'zonghe',skr as skdwmc,skfzh,skfkhx,sqbxje,zflwb as xzzfl,sqbxje as tyzfje,\n" +
                    "(case zcqd when 0 then(select xmdm  from uf_zyczbksr_xj where id=(select REPLACE(hpssjid,'28_','') from uf_gxmdygnkm where id=xzkmdm )) \n" +
                    "else (select xmdm from uf_fzysrxm where id=zcdfczbkxm) end)as xmdm\n" +
                    "from "+tableName+" where zzlx=1 and requestid="+requestId;
        }else{
            sql = "select (select COUNT(*) jilu from "+tableName+"_dt2 fmd2\n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id\n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id\n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 1 and fm.requestid = "+requestId+") bishu, \n" +
                    "(select SUM(tyzfje) from "+tableName+"_dt2 fmd2  \n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id  \n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id \n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 1 and fm.requestid = "+requestId+") zonghe, \n" +
                    "\tfmd2.skr as skdwmc,skfzh,skfkhx,tyzfje,zflwb as xzzfl,uzx.xmdm \n" + //"\tupo.skdwmc,skfzh,skfkhx,tyzfje,zflwb as xzzfl,uzx.xmdm \n"
                    "from "+tableName+"_dt2 fmd2  \n" +
                    "\tLEFT JOIN uf_zyczbksr_xj uzx on fmd2.mainid = uzx.id  \n" +
                    "\tLEFT JOIN uf_payee_ompany upo on fmd2.skdwmc = upo.id \n" +
                    "\tLEFT JOIN "+tableName+" fm on fmd2.mainid = fm.id \n" +
                    "\twhere zzlx = 1 and fm.requestid = "+requestId+"";
        }

        logger.info("对私sql语句打印:"+sql);
        rs.executeQuery(sql);
        //存储返回的数据
        List list = new ArrayList();
        RecordSet getXMDM = new RecordSet();
        while (rs.next()){
            Map map = new HashMap();
            map.put("bishu",rs.getString("bishu")); //笔数
            map.put("zonghe",rs.getString("zonghe"));   //对私金额总和
            map.put("skdwmc",rs.getString("skdwmc"));//收款户名 （表字段：收款单位名称）
            map.put("skfzh",rs.getString("skfzh")); //收款方账号
            map.put("skfkhx",rs.getString("skfkhx"));//	收款方开户行
            map.put("tyzfje",rs.getString("tyzfje"));   //收款金额(表字段：同意支付金额)
            map.put("xzzfl",rs.getString("xzzfl")); //支付令（无则空）	(表字段：选择支付令)
            //map.put("xmdm",rs.getString("xmdm"));   //项目编码
            map.put("xmdm","");   //项目编码
            if(workflowId.equals(YS06Workflowid) || workflowId.equals(YS10Workflowid) || workflowId.equals(YS27Workflowid) ){
                String xmdmSQL = "select  uzx.xmdm  from "+tableName+" fm left join uf_gxmdygnkm ug on fm.xzkmdm=ug.id\n" +
                        "left join uf_zyczbksr_xj uzx on uzx.id=REPLACE(ug.hpssjid,'28_','')\n" +
                        "where fm.requestid="+requestId;
                getXMDM.execute(xmdmSQL);
                if (getXMDM.next()){
                    map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                }
            }else{
                String sql1 = "select top 1 xzkmdm from "+tableName+"_dt1 where mainid="+billid+" and zfl='"+rs.getString("xzzfl")+"'";
                getXMDM.execute(sql1);
                if(getXMDM.next()){
                    String xzkmdm = getXMDM.getString("xzkmdm");
                    String sql2 = "select a.xmdm from uf_zyczbksr_xj a, uf_gxmdygnkm b where b.id='"+xzkmdm+"' and a.id=REPLACE(b.hpssjid,'28_','')";
                    getXMDM.execute(sql2);
                    if(getXMDM.next()){
                        map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                    }
                }
            }
            list.add(map);
        }
        logger.info("数据list:"+JSON.toJSON(list));
        return list;
    }


    /**
     * table4：公务卡(字段未补齐)
     * @param mapParam
     * @return
     */
    public Object businessCard(Map<String,Object> mapParam){
        logger.info("===========预算执行通知单接口日志输出 公务卡===========");
        String requestId = mapParam.get("requestId").toString();
        if(requestId.contains(",")){
            requestId = requestId.substring(0,requestId.indexOf(','));
        }
        String workflowId = generalDao.getRequestIdDao(requestId);

        if(workflowId.isEmpty() || workflowId.equals("")){
            logger.info("该请求requestid没有关联的workflowId流程id  workflowId="+workflowId);
            return new ArrayList<>();
        }

        String tableName = generalDao.getTableNameByWorkFlowId(workflowId);
        String billid = generalDao.getBillidByRequestid(tableName,requestId);
        RecordSet rs = new RecordSet();
        String sqlId = "select id from "+tableName+" where requestid = " + mapParam.get("requestId")+"";
        rs.executeQuery(sqlId);
        rs.next();

        String sql = "";
        BaseBean baseBean = new BaseBean();
        String YS27Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS27Workflowid");//费用信息和支付信息主表
        String YS10Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS10Workflowid");//费用信息和支付信息主表
        String YS06Workflowid = baseBean.getPropValue("ReimbursementWorkflowid", "YS06Workflowid");//费用信息主表

        if(YS27Workflowid.equals(workflowId) || YS10Workflowid.equals(workflowId)){
            //借款流程
            sql = "select 1 as 'bishu',sqbxje as 'zonghe',sqbxje as 'gwkskjezh'\n" +
                    ",(select lastname from hrmresource where id=bxr)as bxr,skr,\n" +
                    "skfzh as gwkh,skfkhx,sqbxje as 'tyzfje',gwksksj,gwkskje,zflwb as xzzfl,\n" +
                    "(case zcqd when 0 then(select xmdm  from uf_zyczbksr_xj where id=(select REPLACE(hpssjid,'28_','') from uf_gxmdygnkm where id=xzkmdm )) \n" +
                    "else (select xmdm from uf_fzysrxm where id=zcdfczbkxm) end)as xmdm\n" +
                    "from "+tableName+" where zzlx=2 and requestid="+requestId;
        }else{
//            sql = "select distinct * from (SELECT\n" +
//                    "\t(\n" +
//                    "\t\tSELECT\n" +
//                    "\t\t\tCOUNT (1)\n" +
//                    "\t\tFROM\n" +
//                    "\t\t\t"+tableName+"_dt2 fmd2\n" +
//                    "\t\tLEFT JOIN uf_zyczbksr_xj uzx ON fmd2.mainid = uzx.id\n" +
//                    "\t\tLEFT JOIN uf_yggwktz uy ON uy.id = fmd2.xzgwk\n" +
//                    "\t\tLEFT JOIN uf_payee_ompany upo ON fmd2.skdwmc = upo.id\n" +
//                    "\t\tLEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id\n" +
//                    "\t\tWHERE\n" +
//                    "\t\t\tzzlx = 2\n" +
//                    "\t\tAND fm.requestid = "+requestId+"\n" +
//                    "\t) bishu,\n" +
//                    "\t(\n" +
//                    "\t\tSELECT\n" +
//                    "\t\t\tSUM (tyzfje)\n" +
//                    "\t\tFROM\n" +
//                    "\t\t\t"+tableName+"_dt2 fmd2\n" +
//                    "\t\tLEFT JOIN uf_zyczbksr_xj uzx ON fmd2.mainid = uzx.id\n" +
//                    "\t\tLEFT JOIN uf_yggwktz uy ON uy.id = fmd2.xzgwk\n" +
//                    "\t\tLEFT JOIN uf_payee_ompany upo ON fmd2.skdwmc = upo.id\n" +
//                    "\t\tLEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id\n" +
//                    "\t\tWHERE\n" +
//                    "\t\t\tzzlx = 2\n" +
//                    "\t\tAND fm.requestid = "+requestId+"\n" +
//                    "\t) zonghe,\n" +
//                    "\t(\n" +
//                    "\t\tSELECT\n" +
//                    "\t\t\tSUM (gwkskje)\n" +
//                    "\t\tFROM\n" +
//                    "\t\t\t"+tableName+"_dt2 fmd2\n" +
//                    "\t\tLEFT JOIN uf_zyczbksr_xj uzx ON fmd2.mainid = uzx.id\n" +
//                    "\t\tLEFT JOIN uf_yggwktz uy ON uy.id = fmd2.xzgwk\n" +
//                    "\t\tLEFT JOIN uf_payee_ompany upo ON fmd2.skdwmc = upo.id\n" +
//                    "\t\tLEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id\n" +
//                    "\t\tWHERE\n" +
//                    "\t\t\tzzlx = 2\n" +
//                    "\t\tAND fm.requestid = "+requestId+"\n" +
//                    "\t) gwkskjezh,\n" +
//                    "\thr.lastname bxr,\n" +
//                    "\tgzk.gzh,\n" + //从工资卡表中取工资号
//                    //"\tuy.gwkh,\n" + //公务卡表中公务卡号
//                    "\tfmd2.skfzh as gwkh,"+
//                    "\tgwksksj,\n" +
//                    "\tgwkskje,\n" +
//                    "\ttyzfje,\n" +
//                    "\tzflwb as xzzfl,\n" +
//                    "\txmdm,\n" +
//                    "\tfmd2.skr\n"+
//                    "FROM\n" +
//                    "\t"+tableName+"_dt2 fmd2\n" +
//                    "LEFT JOIN uf_zyczbksr_xj uzx ON fmd2.mainid = uzx.id\n" +
//                    //"LEFT JOIN uf_yggwktz uy ON uy.id = fmd2.xzgwk\n" +
//                    "LEFT JOIN uf_payee_ompany upo ON fmd2.skdwmc = upo.id\n" +
//                    "LEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id\n" +
//                    "LEFT JOIN hrmresource hr on fmd2.bxr = hr.id\n" +
//                    "LEFT JOIN uf_yggzktz gzk on gzk.ygxm=fmd2.bxr\n "+ //huangwm 新增
//                    "WHERE\n" +
//                    "\tzzlx = 2\n" +
//                    "AND fm.requestid = "+requestId+"\n" +
//                    " ) t ";
            sql = " SELECT (\n" +
                    "\tSELECT COUNT(fmd2.id) FROM "+tableName+"_dt2 fmd2 LEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id   WHERE fmd2.zzlx = 2 AND fm.requestid = "+requestId+"\n" +
                    " )AS bishu, (\n" +
                    "\tSELECT SUM(fmd2.tyzfje) FROM "+tableName+"_dt2 fmd2 LEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id   WHERE fmd2.zzlx = 2 AND fm.requestid = "+requestId+"\n" +
                    " )AS zonghe,(\n" +
                    "\tSELECT SUM(fmd2.gwkskje) FROM "+tableName+"_dt2 fmd2 LEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id   WHERE fmd2.zzlx = 2 AND fm.requestid = "+requestId+"\n" +
                    " )AS gwkskjezh, fmd2.gwkskje,fmd2.gwksksj,fmd2.xzgwk,fmd2.tyzfje,fmd2.zflwb AS xzzfl,hr.lastname AS bxr\n" +
                    " ,gzk.gzh,fmd2.skr,uy.gwkh\n" +
                    " FROM "+tableName+"_dt2 fmd2 LEFT JOIN "+tableName+" fm ON fmd2.mainid = fm.id\n" +
                    " LEFT JOIN hrmresource hr on fmd2.bxr = hr.id\n" +
                    " LEFT JOIN uf_yggzktz gzk on gzk.ygxm=fmd2.bxr\n" +
                    " LEFT JOIN uf_yggwktz uy ON uy.id = fmd2.xzgwk\n" +
                    " WHERE fmd2.zzlx = 2 AND fm.requestid ="+requestId;
        }

        logger.info("公务卡sql语句打印:"+ sql);
        rs.executeQuery(sql);
        //存储返回的数据
        List list = new ArrayList();
        RecordSet getXMDM = new RecordSet();
        while (rs.next()){
            Map map = new HashMap();
            map.put("bishu",rs.getString("bishu")); //笔数
            map.put("zonghe",rs.getString("zonghe"));   //同意还款金额总和
            map.put("gwkskjezh",rs.getString("gwkskjezh")); //刷卡金额总和
            map.put("bxr",rs.getString("skr")); //持卡人
            map.put("gzh",rs.getString("gzh")); //工资号
            map.put("gwkh",rs.getString("gwkh"));//	公务卡卡号
            map.put("gwksksj",rs.getString("gwksksj"));   //公务卡刷卡时间
            map.put("gwkskje",rs.getString("gwkskje")); // 公务卡刷卡金额
            map.put("tyzfje",rs.getString("tyzfje"));   //同意还款金额
            map.put("xzzfl",rs.getString("xzzfl"));   //选择支付令
            //map.put("xmdm",rs.getString("xmdm"));   //项目编码
            map.put("xmdm","");   //项目编码
            if(workflowId.equals(YS06Workflowid) || workflowId.equals(YS10Workflowid) || workflowId.equals(YS27Workflowid) ){
                String xmdmSQL = "select  uzx.xmdm  from "+tableName+" fm left join uf_gxmdygnkm ug on fm.xzkmdm=ug.id\n" +
                        "left join uf_zyczbksr_xj uzx on uzx.id=REPLACE(ug.hpssjid,'28_','')\n" +
                        "where fm.requestid="+requestId;
                getXMDM.execute(xmdmSQL);
                if (getXMDM.next()){
                    map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                }
            }else{
                String sql1 = "select top 1 xzkmdm from "+tableName+"_dt1 where mainid="+billid+" and zfl='"+rs.getString("xzzfl")+"'";
                getXMDM.execute(sql1);
                if(getXMDM.next()){
                    String xzkmdm = getXMDM.getString("xzkmdm");
                    String sql2 = "select a.xmdm from uf_zyczbksr_xj a, uf_gxmdygnkm b where b.id='"+xzkmdm+"' and a.id=REPLACE(b.hpssjid,'28_','')";
                    getXMDM.execute(sql2);
                    if(getXMDM.next()){
                        System.err.println("获取项目代码sql2："+sql2+"  "+getXMDM.getString("xmdm"));
                        map.put("xmdm",getXMDM.getString("xmdm"));   //项目编码
                    }
                }
            }
            list.add(map);
        }
        logger.info("数据list:"+JSON.toJSON(list));
        return list;
    }


    @POST
    @Path("/capitalSources")
    @Produces(MediaType.APPLICATION_JSON)
    public Object budgetMaster(Map<String,Object> mapParam){
        List list = new ArrayList();
        list.add(this.capitalSource(mapParam)); //table1
        list.add(this.publicTransfer(mapParam)); //对公
        list.add(this.forThePrivate(mapParam)); //对私
        list.add(this.businessCard(mapParam)); //公务卡

        /*2021年8月22日21:49:19 huangwm 增加用于返回预算通知单号*/
        RecordSet rs = new RecordSet();
        String requestid = (String) mapParam.get("requestId");
        rs.execute("select * from uf_szxtdzh where bxlc='"+requestid+"'");
        rs.next();
        String ystzdh = rs.getString("tzdh");
        Map<String,String> map = new HashMap<>();
        map.put("requestid", requestid);
        map.put("ystzdh", ystzdh);
        list.add(map);//预算通知单号y
        list.add(this.getBudgetSourceInfo(mapParam));//预算来源
        /*2021年8月22日21:49:19 huangwm 增加用于返回预算通知单号*/

        return JSON.toJSON(list);
    }

    public List<String> getMainWorkflowid(){
        List<String> mainWorkflowid = new ArrayList<>();
        BaseBean baseBean = new BaseBean();
        mainWorkflowid.add(baseBean.getPropValue("ReimbursementWorkflowid", "YS06Workflowid"));
        mainWorkflowid.add(baseBean.getPropValue("ReimbursementWorkflowid", "YS10Workflowid"));
        mainWorkflowid.add(baseBean.getPropValue("ReimbursementWorkflowid", "YS27Workflowid"));//纯主表
        return mainWorkflowid;
    }

    public List getBudgetSourceInfo(Map<String,Object> mapParam){
        List budetSourceInfo = new ArrayList<>();
        List<String> mainWorkflowid = getMainWorkflowid();
        String requestId = mapParam.get("requestId").toString();
        if(requestId.contains(",")){
            requestId = requestId.substring(0,requestId.indexOf(','));
        }
        String workflowId = generalDao.getRequestIdDao(requestId);
        logger.info("workflowId:"+workflowId);

        if(workflowId.isEmpty() || workflowId.equals("")){
            logger.info("该请求requestid没有关联的workflowId流程id  workflowId="+workflowId);
            return new ArrayList<>();
        }
        String tableName = generalDao.getTableNameByWorkFlowId(workflowId);
        String sql ="";
        RecordSet rs = new RecordSet();
        if(!mainWorkflowid.contains(workflowId)){
            sql = "select uf_bxkmdm.kmdm as bxkmdm,uf_bxkmdm.kmmc as bxkmmc,uf_bxkmdm.kmmc+'('+ uf_bxkmdm.kmdm +')' as bxkmxx,selectname as zflx,(case zcqd when 0 then '财政拨款' else '非财政拨款' end) as zcqd ,\n" +
                    "dygnkmmc as xzkmdm,zcgnkm,uf_srysfl.mc as fzysrfl,uf_fzysrxm.xmmc as zcdfczbkxm,(case ysly when 0 then '当年预算' else '上年结转' end) as ysly,tybxje,f.bmjjzcfl,f.zfjjzcfl, \n                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        " +
                    "(select sum(tybxje) as zonghe\n" +
                    "\tfrom "+tableName+"_dt1 f \n" +
                    "\tleft join uf_bxkmdm on f.bxkmdm=uf_bxkmdm.id \n" +
                    "\tleft join uf_gxmdygnkm on f.xzkmdm=uf_gxmdygnkm.id left join workflow_SelectItem \n" +
                    "\ton f.zflx=workflow_SelectItem.selectvalue left join uf_srysfl on f.fzysrfl=uf_srysfl.id \n" +
                    "\tleft join uf_fzysrxm on f.zcdfczbkxm=uf_fzysrxm.id where   workflow_SelectItem.fieldid=24377 \n" +
                    "\tand f.mainid=(select id from formtable_main_905 where requestid="+requestId+")) as zonghe \n"+
                    "\tfrom "+tableName+"_dt1"+" f \n" +
                    "\tleft join uf_bxkmdm on f.bxkmdm=uf_bxkmdm.id \n" +
                    "\tleft join uf_gxmdygnkm on f.xzkmdm=uf_gxmdygnkm.id \n" +
                    "\tleft join workflow_SelectItem on f.zflx=workflow_SelectItem.selectvalue \n" +
                    "\tleft join uf_srysfl on f.fzysrfl=uf_srysfl.id \n" +
                    "\tleft join uf_fzysrxm on f.zcdfczbkxm=uf_fzysrxm.id \n" +
                    "\twhere   workflow_SelectItem.fieldid=24377 and f.mainid=(select id from "+tableName+" where requestid="+requestId+")";
        }else{
            sql = "select uf_bxkmdm.kmdm as bxkmdm,uf_bxkmdm.kmmc as bxkmmc,uf_bxkmdm.kmmc+'('+ uf_bxkmdm.kmdm +')' as bxkmxx,selectname as zflx,(case zcqd when 0 then '财政拨款' else '非财政拨款' end) as zcqd ," +
                    "\tdygnkmmc as xzkmdm,zcgnkm,uf_srysfl.mc as fzysrfl,uf_fzysrxm.xmmc as zcdfczbkxm,(case ysly when 0 then '当年预算' else '上年结转' end) as ysly,tybxje,f.bmjjzcfl,f.zfjjzcfl,tybxje as zonghe " +
                    "\tfrom "+tableName+" f " +
                    "\tleft join uf_bxkmdm on f.bxkmdm=uf_bxkmdm.id " +
                    "\tleft join uf_gxmdygnkm on f.xzkmdm=uf_gxmdygnkm.id " +
                    "\tleft join workflow_SelectItem on f.zflx=workflow_SelectItem.selectvalue " +
                    "\tleft join uf_srysfl on f.fzysrfl=uf_srysfl.id " +
                    "\tleft join uf_fzysrxm on f.zcdfczbkxm=uf_fzysrxm.id " +
                    "\twhere   workflow_SelectItem.fieldid=24377 and f.requestid="+requestId;
        }
        System.err.println(sql);
        rs.execute(sql);
        while (rs.next()){
            Map map = new HashMap();
            map.put("bxkmdm",rs.getString("bxkmdm"));
            map.put("bxkmmc",rs.getString("bxkmmc"));
            map.put("bxkmxx",rs.getString("bxkmxx"));
            map.put("bmjjzcfl",rs.getString("bmjjzcfl"));
            map.put("zfjjzcfl",rs.getString("zfjjzcfl"));
            map.put("zflx",rs.getString("zflx"));
            map.put("zcqd",rs.getString("zcqd"));
            map.put("xzkmdm",rs.getString("xzkmdm"));
            map.put("zcgnkm",rs.getString("zcgnkm"));
            map.put("fzysrfl",rs.getString("fzysrfl"));
            map.put("zcdfczbkxm",rs.getString("zcdfczbkxm"));
            map.put("ysly",rs.getString("ysly"));
            map.put("tybxje",rs.getString("tybxje"));
            map.put("zonghe",rs.getString("zonghe"));
            budetSourceInfo.add(map);
        }
        return budetSourceInfo;
    }
}
